package LawsonTransformJava.pgm;
import java.sql.*;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.Date;
import java.io.*;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;

import LawsonTransformJava.xref.CrossRef;
import LawsonTransformJava.xref.XrefPlanCode;
import LawsonTransformJava.xref.XrefPosition;

import LawsonTransformJava.dao.DAOBenefit;
import LawsonTransformJava.dao.DAOFactoryOracleDB;
import LawsonTransformJava.dao.DAObject;
import LawsonTransformJava.data.TblBenefit;

/**
 * @author edavis
 *
 */
public class BN531 extends DAObject implements 
DAOBenefit, Runnable {

	private TblBenefit ben = new TblBenefit();

	private XrefPlanCode xPlanCodes = null;

	/**
	 * 
	 * @param output - Output file to write records
	 * @throws SQLException
	 */
	public BN531(File output, File errOutput, File errReport) throws SQLException {
		try {
			this.output = new FileWriter(this.outputFile = output);
			this.errOutput = new FileWriter(this.errOutputFile = errOutput);
			this.errReport = new Report(errReport, new Date());

			// initialization 
			init(false);
		} catch (IOException e) {
			// Could no write properties file
			System.out.println("IOException: " + e);
		}
	}

	/**
	 * @param output 		Output file to write records
	 * @param allowBlanks	Allow Blanks if a Cross Reference field is not found.
	 * 						Only allowed for non-required fields.
	 * @throws SQLException
	 */
	public BN531(File output, File errOutput, File errReport, boolean allowBlanks) throws SQLException {
		try {
			this.output = new FileWriter(this.outputFile = output);
			this.errOutput = new FileWriter(this.errOutputFile = errOutput);
			this.errReport = new Report(errReport, new Date());

			// initialization 
			init(allowBlanks);
		} catch (IOException e) {
			// Could no write properties file
			System.out.println("IOException: " + e);
		}
	}

	/**
	 * Initialization method that creates the data connection, cross reference DB connection and creates the
	 * appropriate Cross Reference mappings that are used in this Conversion Transformation Program.
	 * 
	 * @param allowBlanks - Allow Blanks if a Cross Reference field is not found.
	 * 						Only allowed for non-required fields.
	 * @throws IOException
	 * @throws SQLException
	 */
	private void init(boolean allowBlanks) throws IOException, SQLException {
		// Set the data variables
		data = DAOFactoryOracleDB.getDAOOracleDB(true);
		xPlanCodes = new XrefPlanCode();

		errReport.setPgmName(this.getClass().getSimpleName());

		sqlData = "SELECT * FROM " + data.getSchema() + "." + TABLE_NAME;
		stmtData = data.getDataStatement();	    
		countStmtData = data.getCountStatement();
}

	public TblBenefit findBenefit() {
		try {
			if (whereClause==null || whereClause.equals("")) {
				whereClause = " WHERE to_char(" + TABLE_NAME + ".STOP_DATE, 'yyyymmdd') = '17000101'" +
						" OR to_char(" + TABLE_NAME + ".STOP_DATE, 'yyyymmdd') > '20130101'";
			} else {
				whereClause = whereClause + " AND (to_char(" + TABLE_NAME + ".STOP_DATE, 'yyyymmdd') = '17000101'" +
						" OR to_char(" + TABLE_NAME + ".STOP_DATE, 'yyyymmdd') > '20130101')";
			}
			sqlData = "SELECT " + TABLE_NAME + ".* " +
			          " FROM " + data.getSchema() + "." + TABLE_NAME +
			          " " + (whereClause==null?"":whereClause);
			rslt = data.getDataStatement().executeQuery(sqlData);
			rsmd = rslt.getMetaData();
			if (rslt.next()){
				hasMoreRecs = true;
				ben = (TblBenefit) createObject(rslt, new TblBenefit());
			}
			else
				hasMoreRecs = false;
			return ben;
		} catch (SQLException e) {
			// Could not connect to the database
			System.out.println("SQLException: " + e);
		} catch (InvocationTargetException ite) {
			System.out.println("InvocationException: " + ite);
		} catch (IllegalAccessException iae) {
			System.out.println("IllegalAccessException: " + iae);
		}
		return null;
	}

	public TblBenefit findBenefit(String employeeNumber) {
		try {
			if (whereClause==null || whereClause.equals("")) {
				whereClause = " WHERE " + TABLE_NAME + ".EMPLOYEE=" + employeeNumber + " AND (to_char(" + TABLE_NAME + ".STOP_DATE, 'yyyymmdd') = '17000101'" +
						" OR to_char(" + TABLE_NAME + ".STOP_DATE, 'yyyymmdd') > '20130101')";
			} else {
				whereClause = whereClause + " " + TABLE_NAME + ".EMPLOYEE=" + employeeNumber + " AND (to_char(" + TABLE_NAME + ".STOP_DATE, 'yyyymmdd') = '17000101'" +
						" OR to_char(" + TABLE_NAME + ".STOP_DATE, 'yyyymmdd') > '20130101')";
			}
			whereClause = "WHERE " + TABLE_NAME + ".EMPLOYEE=" + employeeNumber;
			sqlData = "SELECT " + TABLE_NAME + ".* " +
			          " FROM " + data.getSchema() + "." + TABLE_NAME +
			          " " + (whereClause==null?"":whereClause);
			rslt = data.getDataStatement().executeQuery(sqlData);
			rsmd = rslt.getMetaData();
			rslt.next();
			ben = (TblBenefit) createObject(rslt, new TblBenefit());
			return ben;
		} catch (SQLException e) {
			// Could not connect to the database
			System.out.println("SQLException: " + e);
		} catch (InvocationTargetException ite) {
			System.out.println("InvocationException: " + ite);
		} catch (IllegalAccessException iae) {
			System.out.println("IllegalAccessException: " + iae);
		}
		return new TblBenefit();
	}

	public TblBenefit nextBenefit() {
		try {
			if (rslt.next()){
				hasMoreRecs = true;
				ben = (TblBenefit) createObject(rslt, new TblBenefit());
			}
			else
				hasMoreRecs = false;
			return ben;
		} catch (SQLException e) {
			// Could not connect to the database
			System.out.println("SQLException: " + e);
		} catch (InvocationTargetException ite) {
			System.out.println("InvocationException: " + ite);
		} catch (IllegalAccessException iae) {
			System.out.println("IllegalAccessException: " + iae);
		}
		return null;
	}

	public boolean hasMore () {
		return hasMoreRecs;
	}

	public int countBenefits() {
		try {
			countRecs = data.getCountStatement().executeQuery("SELECT COUNT(*) " +
					"FROM " + data.getSchema() + "." + TABLE_NAME + " " +
					(whereClause==null?"":whereClause));
			countRecs.next();
			return recCount=countRecs.getInt(1);
		} catch (SQLException e) {
			// Could not connect to the database
			System.out.println("SQLException: " + e);
		}
		return 0;
	}

	public void run() {

		try {
			//Update current date/time
			System.out.println(errReport.getName() + " Start  " + Report.getDateFormat().format(new Date()));

			//@ TODO DEBUG BEGIN - remove lines
			//		this.setWhereClause("WHERE EMPLOYEE=100466");
			//@ TODO DEBUG END   - remove lines
			ben = findBenefit();
			while (hasMore()) {
//				output.write(writeBN531Layout(ben) + "\n");
				writeBN531Layout(ben);
				ben = nextBenefit();
			}
			//Update current date/time
			System.out.println(errReport.getName() + " Finish " + Report.getDateFormat().format(new Date()));

			//Write Record Counts
			errReport.writeRecordCounts(this.countBenefits());
			
			//Reclaim Resources
			fileCleanup();

		} catch (IOException e) {
			// Could not write properties file
			// Could not create output file
			System.out.println("IOException: " + e);
		}

	}

	//Output the HR511 format
	public void writeBN531Layout(TblBenefit ben) throws IOException {

		String writeValue = null;

		writeValue =
				"A" + "," +
				"1" + "," +
				ben.getEfd_start_date() + "," +
				ben.getEmployee() + "," +
				ben.getPlan_type() + "," +
				// @ TODO Add xlate when defined.
				xPlanCodes.xlate(ben.getPlan_code()) + "," +	//Plan Code
//				ben.getPlan_code() + "," +						//Plan Code
				ben.getCov_option() + "," +
				ben.getMultiple() + "," +
				ben.getCover_amt() + "," +
				ben.getPay_rate() + "," +
				ben.getPct_amt_flag() + "," +
				ben.getStart_date() + "," +
				ben.getPre_aft_flag() + "," +
				ben.getSmoker() + "," +
				ben.getEmp_pre_cont() + "," +
				ben.getEmp_aft_cont() + "," +
				ben.getStop_date() + "," +
				ben.getPend_evidence() + "," +
				ben.getYtd_cont() + "," +
				ben.getCreation_date() + "," +
				ben.getUpd_date() + "," +
				ben.getUser_id() + "," +
				ben.getTime_stamp() + "," +
				"0," +
				"0," +
				"0," +
				"\"AI\"," +						//Reason
				"2," +							//MemberId
//				ben.getPrem_group() + "," +		//Employee Group
				"," +							//Employee Group
				"," +
				"0," +
				ben.getDed_start_date() + "," +
				ben.getDed_stop_date() + "," +
				ben.getEmp_ytd_cont()
			;
				
			if (CrossRef.isError()) {
				//Increment Record in Error
				errReport.addErrorCnt();
				//Write to Error File
				this.errOutput.write(writeValue + "\n");
				this.errOutput.flush();
				CrossRef.resetError();
			}
			else {
				//Increment Record Written
				errReport.addWrittenCnt();
				//Write to Load File
				this.output.write(writeValue + "\n");
				this.output.flush();
			}
	}
	
	public void fileCleanup() throws IOException {
		errReport.flush();
		errReport.close();
		output.flush();
		output.close();
		errOutput.flush();
		errOutput.close();
	}
}